package com.sltx.service.impl;


import com.sltx.dao.aaa.ExcelMapper;
import com.sltx.service.ExcelService;
import com.sltx.util.PageData;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service
public class ExcelServiceImpl implements ExcelService {


    private static final String url = "jdbc:mysql://192.201.58.3:3306/sjs_qysj?serverTimezone=CST&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&useSSL=false";
    private static final String dd = "com.mysql.jdbc.Driver";
    private static final String username = "qysj_rw";
    private static final String password = "jeeMot3rf5aqypI";
    Connection conn = null;
    Statement stmt = null;

    public ExcelServiceImpl() {
    }

    ;

    @Autowired
    private ExcelMapper excelMapper;


    @Override
    public String insertExcel(List<Map<String, Object>> list, PageData pd) {
        try {
            Class.forName(dd);
            conn = DriverManager.getConnection(url, username, password);
            // 关闭自动执行
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            for (int i = 0; i < list.size(); i++) {
                pd.put("entname", list.get(i).get("企业名称").toString());
                pd.put("unicode", list.get(i).get("统一信用代码").toString());
                pd.put("year", list.get(i).get("年份").toString());
                pd.put("month", list.get(i).get("月份").toString());
                int num = excelMapper.getEntNameNum(pd);
                if (num == 0) {
                    String sql = "insert into sjs_zsgl_skje(entname,skje,zje,zf,qj,unicode,industry,industry_dl,reg_capital,reg_address,currency,year,month) values ('" +
                            list.get(i).get("企业名称") + "','" +
                            list.get(i).get("税款金额") + "','" +
                            list.get(i).get("增减额") + "','" +
                            list.get(i).get("增幅") + "','" +
                            list.get(i).get("区级") + "','" +
                            list.get(i).get("统一信用代码") + "','" +
                            list.get(i).get("行业") + "','" +
                            list.get(i).get("行业大类") + "','" +
                            list.get(i).get("注册资本") + "','" +
                            list.get(i).get("注册地址") + "','" +
                            list.get(i).get("币种") + "','" +
                            list.get(i).get("年份") + "','" +
                            list.get(i).get("月份") + "');";
                    System.out.println(sql);
                    stmt.addBatch(sql);
                } else {
                    String sql = "update  sjs_zsgl_skje set skje = '" +
                            list.get(i).get("税款金额") + "', zje = '" +
                            list.get(i).get("增减额") + "', zf = '" +
                            list.get(i).get("增幅") + "', qj = '" +
                            list.get(i).get("区级") + "', industry = '" +
                            list.get(i).get("行业") + "', industry_dl = '" +
                            list.get(i).get("行业大类") + "', reg_capital = '" +
                            list.get(i).get("注册资本") + "', reg_address = '" +
                            list.get(i).get("注册地址") + "', currency = '" +
                            list.get(i).get("币种") +
                            "' where entname = '" +
                            list.get(i).get("企业名称") + "' and unicode = '" +
                            list.get(i).get("统一信用代码") + "' and year = '" +
                            list.get(i).get("年份") + "' and month = '" +
                            list.get(i).get("月份") + "';";
                    System.out.println(sql);
                    stmt.addBatch(sql);
                }
                if (i % 100 == 0 && i != 0) {
                    stmt.executeBatch();
                    conn.commit();
                    stmt.clearBatch();
                }
            }
            stmt.executeBatch();
            conn.commit();
            stmt.clearBatch();
            close();
            return "上传成功";
        } catch (Exception e) {
            return e.toString();
        }
    }

    @Override
    public List<Map> selectEntQyss(PageData pd) {
        List<Map> result = new ArrayList();
        List<Map> maps = excelMapper.selectEntQyss(pd);
        if (maps.size() > 0) {
            for (Map e : maps) {
                if (e.get("entname") == null) {
                    e.put("entname", pd.get("entname"));
                }
                if (e.get("unicode") == null) {
                    e.put("unicode", "");
                }
                if (e.get("industry") == null) {
                    e.put("industry", "");
                }
                if (e.get("industry_dl") == null) {
                    e.put("industry_dl", "");
                }
                if (e.get("reg_capital") == null) {
                    e.put("reg_capital", "");
                }
                result.add(e);
            }
        } else {
            Map<String, Object> e = new HashMap<>();
            e.put("entname", pd.get("entname"));
            e.put("unicode", "");
            e.put("industry", "");
            e.put("industry_dl", "");
            e.put("reg_capital", "");
            result.add(e);
        }
        return result;
    }

    @Override
    public List<Map> Entyear(PageData pd) {
        List<Map> result = new ArrayList();
        result = excelMapper.Entyear(pd);
        return result;
    }

    @Override
    public List<Map> Entmonth(PageData pd) {
        List<Map> result = new ArrayList();
        result = excelMapper.Entmonth(pd);
        return result;
    }


    @Override
    public int updateSkje(PageData pd) {
        return excelMapper.updateSkje(pd);

    }

    public void close() {
        try {
            this.conn.close();
            this.stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
